package net.zjitc.dao.Impl;

import net.zjitc.domain.Admin;
import net.zjitc.dao.Dao;
import net.zjitc.domain.Category;
import net.zjitc.domain.CategorySecond;
import net.zjitc.domain.User;
import org.apache.commons.beanutils.BeanMap;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.dbutils.QueryRunner;

import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.BeanMapHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import utils.dbutils.C3P0utils;

import java.lang.reflect.InvocationTargetException;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

/**
 * Created by guo on 2017/11/11.
 */
public class DaoImpl implements Dao {
    public Admin fingUsernameAndPassword(String username, String password) {
        QueryRunner queryRunner=new QueryRunner(C3P0utils.getDs());
        String sql="select * from adminuser";
        Admin admins=null;
        try {
            admins= (Admin) queryRunner.query(sql,new BeanHandler(Admin.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return admins;
    }

    public List<User> findAll() {
        QueryRunner queryRunner=new QueryRunner(C3P0utils.getDs());
        String sql="select * from user";
        List<User> list=null;
        try {
            list= (List<User>) queryRunner.query(sql,new BeanListHandler(User.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    public int delete(int uid)  {
        QueryRunner queryRunner=new QueryRunner(C3P0utils.getDs());
        String sql="delete from user where uid=?";
        int num=0;
        try {
            num= queryRunner.update(sql,uid);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return num;
    }

    public List<Category> findAllCategory() {
        QueryRunner queryRunner=new QueryRunner(C3P0utils.getDs());
        String sql="select * from category";
        List<Category> list=null;
        try {
            list= (List<Category>) queryRunner.query(sql,new BeanListHandler(Category.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    public int addCategory(Category category) {
        QueryRunner queryRunner=new QueryRunner(C3P0utils.getDs());
        String sql="insert into category(cname) values(?)";
        int num=0;
        try {
            num= queryRunner.update(sql,category.getCname());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return num;
    }

    public int deleteCategory(int cid) {
        QueryRunner queryRunner=new QueryRunner(C3P0utils.getDs());
        String sql="delete from category where cid=?";
        int num=0;
        try {
            num= queryRunner.update(sql,cid);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return num;
    }

    public Category findCategoryId(int cid) {
        QueryRunner queryRunner=new QueryRunner(C3P0utils.getDs());
        String sql="select * from category where cid=?";
        Category category=null;
        try {
            category= (Category) queryRunner.query(sql,new BeanHandler(Category.class),cid);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return category;
    }

    public int updateCategory(Category category) {
        QueryRunner runner = new QueryRunner(C3P0utils.getDs());
        String sql = "update category set cname=? where cid=?";
        int num = 0;
        try {
            num = runner.update(sql, category.getCname(), category.getCid());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return num;
    }

    public List<CategorySecond> findAllCategorySecond() {
        QueryRunner queryRunner=new QueryRunner(C3P0utils.getDs());
        String sql="select * from categorysecond";
        List<CategorySecond> list=null;
        try {
            list= (List<CategorySecond>) queryRunner.query(sql,new BeanListHandler(CategorySecond.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }


    public int deleteSecondById(int csid) {
        QueryRunner queryRunner=new QueryRunner(C3P0utils.getDs());
        String sql="delete from categorysecond where csid=?";
        int num=0;
        try {
            num= queryRunner.update(sql,csid);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return num;
    }

    public CategorySecond findCategorySecondId(String csid) throws SQLException, InvocationTargetException, IllegalAccessException {
        QueryRunner queryRunner=new QueryRunner(C3P0utils.getDs());
        String sql="select * from categorysecond where cid=?";
        Category category=new Category();
        CategorySecond categorySecond=new CategorySecond();
        Map<String,Object> map=queryRunner.query(sql,new MapHandler());
        BeanUtils.populate(category,map);
        categorySecond.setCategory(category);
        return  categorySecond;
    }

    public int addCategorySecond(CategorySecond categorySecond) {
        QueryRunner queryRunner=new QueryRunner(C3P0utils.getDs());
        String sql="insert into categorysecond(csname,csid) values(?,?)";
        int num=0;
        try {
            num= queryRunner.update(sql,categorySecond.getCname(),categorySecond.getCsid());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return num;
    }

}
